Modifying Tables Using ALTER TABLE in MySQL – Complete Notes

The ALTER TABLE statement in MySQL is used to modify the structure of an existing table without recreating it from scratch. It allows database developers to add columns, remove columns, change data types, rename columns, modify constraints, create indexes, and rename tables. It is an essential command for maintaining and updating databases as application requirements change.

MySQL Training Course: JustAcademy MySQL Training

Course Demo: Register for MySQL Course Demo

1. Learning Objectives

After completing this topic, learners will be able to:

• Understand the purpose of the ALTER TABLE statement.
• Add new columns to existing tables.
• Remove unnecessary columns safely.
• Modify column data types and attributes.
• Rename columns and tables.
• Add or remove constraints and indexes.
• Change whether a column allows NULL values.
• Verify table modifications using MySQL commands.
• Identify common errors and apply best practices when changing table structures.

2. What Is ALTER TABLE in MySQL?

ALTER TABLE is a Data Definition Language (DDL) statement that changes the definition of an existing table. It modifies the table's structure rather than directly inserting, updating, or deleting individual records.

For example, suppose a table named employees already contains employee IDs and names. If the business later requires an email address column, you can add it using ALTER TABLE.

ALTER TABLE employees
ADD COLUMN email VARCHAR(150);

This statement adds an email column to the existing table. Existing records will have the column's default value or, if no applicable default is defined, generally NULL when the column permits it.

3. Basic Syntax of ALTER TABLE

ALTER TABLE table_name
alteration_clause;

The alteration_clause specifies the change you want to make, such as adding a column, modifying a column, dropping an index, or renaming a table.

Examples of Common ALTER TABLE Operations

-- Add a column
ALTER TABLE employees
ADD COLUMN email VARCHAR(150);

-- Remove a column
ALTER TABLE employees
DROP COLUMN email;

-- Modify a column definition
ALTER TABLE employees
MODIFY COLUMN employee_name VARCHAR(150) NOT NULL;

-- Rename a column
ALTER TABLE employees
RENAME COLUMN employee_name TO full_name;

-- Rename a table
ALTER TABLE employees
RENAME TO staff;

These examples illustrate different operations. Execute them separately and in the correct order because some statements depend on the table's current structure.

4. Preparing a Database for Practice

Before practising table modifications, create a database and a sample table.

CREATE DATABASE IF NOT EXISTS company_db;
USE company_db;

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(80),
    salary DECIMAL(10,2)
);

Verify the initial structure:

DESCRIBE employees;
SHOW CREATE TABLE employees;

The DESCRIBE command displays column information, while SHOW CREATE TABLE shows the complete table definition, including its constraints and indexes.

5. Adding a New Column Using ADD COLUMN

The ADD COLUMN clause adds a new column to an existing table.

Syntax

ALTER TABLE table_name
ADD COLUMN column_name data_type;

Example: Add an Email Column

ALTER TABLE employees
ADD COLUMN email VARCHAR(150);

This adds an optional email column to the employees table.

Example: Add a Joining Date

ALTER TABLE employees
ADD COLUMN joining_date DATE;

The new joining_date column stores calendar dates.

Example: Add a Column with NOT NULL and DEFAULT

ALTER TABLE employees
ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'Active';

The new column uses Active as its default value. A suitable default is particularly important when adding a non-nullable column to a table that already contains records.

Adding a Column at a Specific Position

MySQL supports FIRST and AFTER to specify a column's position.

ALTER TABLE employees
ADD COLUMN phone VARCHAR(20) AFTER email;

This places phone immediately after email in the table definition.

ALTER TABLE employees
ADD COLUMN record_code VARCHAR(30) FIRST;

This places record_code at the beginning of the column list. Column position usually does not affect query logic when queries explicitly name their columns.

6. Adding Multiple Columns at Once

You can add more than one column in a single ALTER TABLE statement by separating the column definitions with commas.

Example

ALTER TABLE employees
ADD COLUMN email VARCHAR(150),
ADD COLUMN phone VARCHAR(20),
ADD COLUMN joining_date DATE;

This adds three columns to the table in one statement. It can be more convenient than executing separate statements.

Important: Do not run this example after adding the same columns in earlier exercises unless you first check the structure. MySQL will return an error if a column name already exists.

7. Modifying a Column Using MODIFY COLUMN

The MODIFY COLUMN clause changes a column's definition while keeping its existing name. It can change the data type, length, nullability, default, and other column attributes.

Syntax

ALTER TABLE table_name
MODIFY COLUMN column_name new_data_type;

Example: Increase the Length of a Column

ALTER TABLE employees
MODIFY COLUMN employee_name VARCHAR(150) NOT NULL;

This changes the maximum declared length of employee_name from 100 to 150 characters while retaining the NOT NULL requirement.

Example: Change a Data Type

ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(12,2);

This increases the precision available for the salary column while keeping two digits after the decimal point.

Example: Change Nullability

ALTER TABLE employees
MODIFY COLUMN department VARCHAR(80) NOT NULL;

This changes department to a required column. Before applying this change, make sure existing records do not contain NULL values.

Important: When using MODIFY COLUMN, specify the intended complete column definition. Attributes not included in the new definition may not be retained as expected.

8. Renaming a Column Using RENAME COLUMN

The RENAME COLUMN clause changes a column's name without requiring you to specify its data type again.

Syntax

ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

Example

ALTER TABLE employees
RENAME COLUMN employee_name TO full_name;

The column is now called full_name. Its existing data remains associated with that column.

Another Example

ALTER TABLE employees
RENAME COLUMN department TO department_name;

Use descriptive names so that developers can understand what the column stores.

Version note: RENAME COLUMN is available in MySQL 8.0. If you are using an older version, check the syntax supported by that release.

9. Renaming and Changing a Column Using CHANGE COLUMN

The CHANGE COLUMN clause can rename a column and redefine its data type or attributes in the same operation.

Syntax

ALTER TABLE table_name
CHANGE COLUMN old_name new_name new_data_type;

Example

ALTER TABLE employees
CHANGE COLUMN employee_name full_name VARCHAR(150) NOT NULL;

This renames employee_name to full_name and defines the column as VARCHAR(150) NOT NULL.

Unlike RENAME COLUMN, CHANGE COLUMN requires the new column definition. Include all required attributes, such as nullability and defaults, to avoid unintended changes.

10. Removing a Column Using DROP COLUMN

The DROP COLUMN clause permanently removes a column from a table definition.

Syntax

ALTER TABLE table_name
DROP COLUMN column_name;

Example

ALTER TABLE employees
DROP COLUMN phone;

This removes the phone column and the values stored in it. The operation can affect applications, reports, views, and other database objects that depend on the column.

Removing Multiple Columns

ALTER TABLE employees
DROP COLUMN email,
DROP COLUMN joining_date;

MySQL permits multiple alteration clauses in one statement. Check dependencies before removing columns.

Warning: Dropping a column can permanently destroy its data. Back up important information and verify dependencies before executing the statement, especially in production.

11. Renaming an Entire Table

You can rename an existing table using RENAME TO or RENAME.

Syntax

ALTER TABLE old_table_name
RENAME TO new_table_name;

Example

ALTER TABLE employees
RENAME TO staff;

The table is now named staff.

You can verify the new name using:

SHOW TABLES;
DESCRIBE staff;

Renaming a table can affect application queries, views, scripts, and integrations that refer to the old name. Review these dependencies before making the change.

12. Adding a PRIMARY KEY

A primary key uniquely identifies each row in a table. You can add one using ALTER TABLE when the existing data satisfies the primary-key requirements.

Example

CREATE TABLE departments (
    department_code INT NOT NULL,
    department_name VARCHAR(100) NOT NULL
);

ALTER TABLE departments
ADD PRIMARY KEY (department_code);

The department_code column becomes the primary key.

Important: Before adding a primary key, make sure the chosen column contains no NULL values and no duplicate values. A table can have only one primary-key constraint, although that key may contain multiple columns.

13. Adding a UNIQUE Constraint

A UNIQUE constraint prevents duplicate values in a column or combination of columns.

Example

ALTER TABLE staff
ADD CONSTRAINT uq_staff_email UNIQUE (email);

This creates a unique constraint on the email column, provided that column exists and the current data satisfies the uniqueness requirement.

If the table already contains duplicate non-NULL email addresses, MySQL cannot successfully create the unique constraint until the duplicates are resolved.

14. Adding and Removing Indexes

Indexes can help MySQL find records more efficiently. You can add or remove indexes using ALTER TABLE.

Adding an Index

ALTER TABLE staff
ADD INDEX idx_staff_department (department);

This creates an index named idx_staff_department on the department column.

Adding a Composite Index

ALTER TABLE staff
ADD INDEX idx_staff_department_salary (department, salary);

This creates an index on the combination of department and salary. The column order matters because the index is most directly useful for queries that filter or sort using its leading columns.

Removing an Index

ALTER TABLE staff
DROP INDEX idx_staff_department;

Dropping an index may reduce storage and maintenance costs, but it can also slow down queries. Do not remove an index until you understand its usage and whether it supports a constraint.

15. Adding and Removing Foreign Keys

Foreign keys establish relationships between tables and help maintain referential integrity.

Example: Add a Foreign Key

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE staff (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT
);

ALTER TABLE staff
ADD CONSTRAINT fk_staff_department
FOREIGN KEY (department_id)
REFERENCES departments(department_id);

The department_id column in staff now references the corresponding key in departments.

Example: Remove a Foreign Key

ALTER TABLE staff
DROP FOREIGN KEY fk_staff_department;

The foreign-key constraint is removed, but the department_id column remains in the table.

Important: When adding a foreign key, check that existing child values match valid parent keys or are NULL if the column allows it. The referenced and referencing columns must have compatible definitions and suitable indexes. In MySQL, foreign-key support also depends on the storage engine.

16. Changing a Column from NULL to NOT NULL

If a field becomes mandatory, you can modify it to disallow NULL values.

Step 1: Find Existing NULL Values

SELECT *
FROM staff
WHERE department IS NULL;

Step 2: Resolve Missing Values

Review the missing department information and determine the correct values according to the business rules. Do not assign invented information just to satisfy a constraint.

Step 3: Modify the Column

ALTER TABLE staff
MODIFY COLUMN department VARCHAR(80) NOT NULL;

The statement succeeds only when the existing data and new definition satisfy the required constraints.

17. Changing a Column from NOT NULL to NULL

If a column becomes optional, modify its definition to allow NULL.

ALTER TABLE staff
MODIFY COLUMN department VARCHAR(80) NULL;

This permits future records to store NULL in the department column. Existing values remain unchanged.

Always preserve the intended data type, default value, and other attributes when modifying a column definition.

18. Setting or Changing a DEFAULT Value

A default value is used when an insert statement omits a column that has a default defined.

Example: Set a Default Value

ALTER TABLE staff
ALTER COLUMN department SET DEFAULT 'General';

This sets General as the default department for new records where the column is omitted.

Remove a Default Value

ALTER TABLE staff
ALTER COLUMN department DROP DEFAULT;

This removes the explicit default definition. It does not automatically update existing rows.

Note: A default value is different from a NOT NULL constraint. A default does not mean that every explicitly supplied value must be non-null.

19. Changing the Data Type of a Column

The ALTER TABLE ... MODIFY COLUMN statement can change a column's data type.

Example: Change VARCHAR Length

ALTER TABLE staff
MODIFY COLUMN employee_name VARCHAR(200) NOT NULL;

This increases the declared maximum length of the employee name.

Example: Change a Numeric Type

ALTER TABLE staff
MODIFY COLUMN salary DECIMAL(12,2);

This changes the salary column's numeric definition.

Important: Type conversions can fail, truncate data, change values, or cause rounding depending on the conversion and SQL mode. Inspect existing values and test the change before applying it to important data.

20. Changing Column Order

MySQL allows you to reposition a column with FIRST or AFTER when using a supported column alteration.

Move a Column to the First Position

ALTER TABLE staff
MODIFY COLUMN employee_name VARCHAR(100) NOT NULL FIRST;

Move a Column After Another Column

ALTER TABLE staff
MODIFY COLUMN salary DECIMAL(10,2) AFTER department;

When moving a column, specify its intended full definition, including relevant constraints and default values. Column order is mostly a presentation detail; application queries should name columns explicitly rather than depend on their physical order.

21. Making Multiple Changes in One ALTER TABLE Statement

MySQL allows multiple alteration clauses to be combined in a single ALTER TABLE statement, separated by commas.

Example

ALTER TABLE staff
ADD COLUMN email VARCHAR(150),
ADD COLUMN joining_date DATE,
MODIFY COLUMN employee_name VARCHAR(150) NOT NULL;

This adds two columns and changes the employee name column definition in one statement.

Before executing a combined statement, verify that every referenced column exists, every new column name is available, and all data satisfies the resulting constraints.

22. Verifying Table Modifications

After modifying a table, check that the changes were applied correctly.

View the Table Structure

DESCRIBE staff;

View the Complete Table Definition

SHOW CREATE TABLE staff;

List the Tables

SHOW TABLES;

Inspect Indexes

SHOW INDEX FROM staff;

Check Existing Data

SELECT *
FROM staff
LIMIT 10;

These commands help confirm that columns, constraints, indexes, and table names match your expectations.

23. Complete Practical Project: Updating an Employee Database

This practical project demonstrates several ALTER TABLE operations in sequence.

Step 1: Create the Database and Table

CREATE DATABASE IF NOT EXISTS employee_training;
USE employee_training;

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(80),
    salary DECIMAL(10,2)
);

Step 2: Add Email and Joining Date

ALTER TABLE employees
ADD COLUMN email VARCHAR(150),
ADD COLUMN joining_date DATE;

Step 3: Increase the Name Column Length

ALTER TABLE employees
MODIFY COLUMN employee_name VARCHAR(150) NOT NULL;

Step 4: Rename the Department Column

ALTER TABLE employees
RENAME COLUMN department TO department_name;

Step 5: Add a Default Status

ALTER TABLE employees
ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'Active';

Step 6: Add an Index to Email

ALTER TABLE employees
ADD INDEX idx_employees_email (email);

If email addresses must be unique, consider using a UNIQUE constraint instead, after checking the data and deciding how missing email values should be handled.

Step 7: Verify the Final Structure

DESCRIBE employees;
SHOW CREATE TABLE employees;
SHOW INDEX FROM employees;

Project outcome: You will have practised adding columns, changing a column definition, renaming a column, setting a default for a new column, and creating an index.

24. Common ALTER TABLE Errors and Solutions

25. Best Practices for ALTER TABLE

• Back up important data before making destructive structural changes.
• Inspect the current structure using DESCRIBE and SHOW CREATE TABLE.
• Test schema changes in a development or staging environment first.
• Check existing data before adding NOT NULL, UNIQUE, primary-key, or foreign-key constraints.
• Be cautious when dropping columns because their stored data can be lost.
• Use descriptive and consistent names for columns and indexes.
• Preserve intended data types, defaults, nullability, and other attributes when using MODIFY COLUMN or CHANGE COLUMN.
• Consider the effect on applications, views, stored routines, reports, and integrations.
• For large production tables, assess the operation's locking, runtime, disk-space requirements, and online DDL support.
• Use explicit column lists in SELECT and INSERT statements instead of relying on column order.
• Verify the resulting structure after every important modification.
• Apply schema changes through a documented migration process when working with a team.

26. Difference Between ADD, MODIFY, CHANGE, RENAME, and DROP

27. Practice Exercises

Complete these exercises to strengthen your understanding of table modifications.

1. Create an employees table with an ID, name, and department.
2. Add an email column to the table.
3. Add a joining date column after the department column.
4. Increase the length of the employee name column.
5. Rename the department column to department_name.
6. Add a status column with a default value of Active.
7. Create an index on the email column.
8. Remove an unused column from a practice table.
9. Rename a table from employees to staff.
10. Use DESCRIBE and SHOW CREATE TABLE to verify your changes.

Sample Solution

CREATE TABLE practice_employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(80)
);

ALTER TABLE practice_employees
ADD COLUMN email VARCHAR(150);

ALTER TABLE practice_employees
ADD COLUMN joining_date DATE AFTER department;

ALTER TABLE practice_employees
MODIFY COLUMN employee_name VARCHAR(150) NOT NULL;

ALTER TABLE practice_employees
RENAME COLUMN department TO department_name;

ALTER TABLE practice_employees
ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'Active';

ALTER TABLE practice_employees
ADD INDEX idx_practice_email (email);

DESCRIBE practice_employees;
SHOW CREATE TABLE practice_employees;

28. Interview Questions and Answers

Q1. What is ALTER TABLE in MySQL?

Answer: It is a DDL statement used to change the structure of an existing table.

Q2. How do you add a column to an existing table?

Answer: Use ALTER TABLE table_name ADD COLUMN column_name data_type;.

Q3. What is the difference between MODIFY COLUMN and CHANGE COLUMN?

Answer: MODIFY COLUMN changes the definition without renaming the column. CHANGE COLUMN can rename the column and requires its new definition.

Q4. How do you rename a column in MySQL 8.0?

Answer: Use ALTER TABLE table_name RENAME COLUMN old_name TO new_name;.

Q5. How do you delete a column?

Answer: Use ALTER TABLE table_name DROP COLUMN column_name;. This can permanently remove the column's data.

Q6. Can multiple columns be added in one statement?

Answer: Yes. Separate the ADD COLUMN clauses with commas.

Q7. How do you rename a table?

Answer: Use ALTER TABLE old_name RENAME TO new_name;.

Q8. How do you add an index?

Answer: Use ALTER TABLE table_name ADD INDEX index_name (column_name);.

Q9. What should you check before adding NOT NULL?

Answer: Check for existing NULL values and ensure the new column definition is compatible with the existing data.

Q10. How do you verify a table modification?

Answer: Use DESCRIBE table_name;, SHOW CREATE TABLE table_name;, and, when needed, SHOW INDEX FROM table_name;.

Q11. Does ALTER TABLE modify individual records?

Answer: It primarily modifies the table definition, although some structural changes may require MySQL to rebuild or transform stored data.

Q12. Why should you back up before dropping a column?

Answer: Dropping a column removes its stored values, which may be difficult or impossible to recover without a backup.

29. Quick Revision Cheat Sheet

30. Official Documentation and Further Learning

• MySQL 8.0 Documentation: ALTER TABLE Statement
• MySQL 8.0 Documentation: ALTER TABLE Examples
• MySQL 8.0 Documentation: CREATE TABLE Statement
• JustAcademy MySQL Training Course
• Register for MySQL Course Demo

31. Conclusion

The ALTER TABLE statement is an essential MySQL command for maintaining and evolving database structures. It supports adding, modifying, renaming, and dropping columns, updating constraints, managing indexes, and renaming tables. By understanding these operations and checking existing data before making changes, developers can maintain accurate and dependable databases while reducing the risk of data loss.

Continue learning with the JustAcademy MySQL Training Course or register for a MySQL course demo.